﻿Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Runtime.InteropServices
Imports System.Text
Imports System.Workflow.Runtime
Imports System.Xml

Namespace System.Workflow.Runtime.Tracking
    Public NotInheritable Class SqlTrackingQuery
        ' Methods
        Public Sub New()
        End Sub

        Public Sub New(ByVal connectionString As String)
            If (connectionString Is Nothing) Then
                Throw New ArgumentNullException("connectionString")
            End If
            Me._connectionString = connectionString
        End Sub

        Private Sub BuildArtifactParameters(ByVal cmd As SqlCommand, ByVal artifacts As IList([Of] TrackingDataItemValue))
            If ((Not artifacts Is Nothing) AndAlso (artifacts.Count <> 0)) Then
                Dim output As New StringBuilder
                Dim writer As XmlWriter = XmlWriter.Create(output)
                Try 
                    writer.WriteStartDocument
                    writer.WriteStartElement("TrackingDataItems")
                    Dim value2 As TrackingDataItemValue
                    For Each value2 In artifacts
                        writer.WriteStartElement("TrackingDataItem")
                        writer.WriteElementString("QualifiedName", value2.QualifiedName)
                        writer.WriteElementString("FieldName", value2.FieldName)
                        If (Not value2.DataValue Is Nothing) Then
                            writer.WriteElementString("DataValue", value2.DataValue)
                        End If
                        writer.WriteEndElement
                    Next
                    writer.WriteEndElement
                    writer.WriteEndDocument
                Finally
                    writer.Flush
                    writer.Close
                End Try
                Dim parameter As New SqlParameter { _
                    .ParameterName = "@TrackingDataItems", _
                    .SqlDbType = SqlDbType.NText, _
                    .Value = output.ToString _
                }
                cmd.Parameters.Add(parameter)
            End If
        End Sub

        Private Function BuildCommand(ByVal workflowInstanceId As Guid) As SqlCommand
            Dim command As New SqlCommand("[dbo].[GetWorkflows]") { _
                .CommandType = CommandType.StoredProcedure _
            }
            Dim parameter As New SqlParameter { _
                .ParameterName = "@WorkflowInstanceId", _
                .SqlDbType = SqlDbType.UniqueIdentifier, _
                .Value = workflowInstanceId _
            }
            command.Parameters.Add(parameter)
            Return command
        End Function

        Private Function BuildCommand(ByVal opt As SqlTrackingQueryOptions) As SqlCommand
            Dim cmd As New SqlCommand("[dbo].[GetWorkflows]") { _
                .CommandType = CommandType.StoredProcedure _
            }
            Dim parameter As New SqlParameter
            If opt.WorkflowStatus.HasValue Then
                parameter.ParameterName = "@WorkflowStatusId"
                parameter.SqlDbType = SqlDbType.TinyInt
                parameter.Value = opt.WorkflowStatus.Value
                cmd.Parameters.Add(parameter)
                If ((DateTime.MinValue <> opt.StatusMinDateTime) OrElse (DateTime.MaxValue <> opt.StatusMaxDateTime)) Then
                    parameter = New SqlParameter { _
                        .ParameterName = "@StatusMinDateTime", _
                        .SqlDbType = SqlDbType.DateTime _
                    }
                    If (opt.StatusMinDateTime < SqlDateTime.MinValue.Value) Then
                        parameter.Value = SqlDateTime.MinValue.Value
                    Else
                        parameter.Value = opt.StatusMinDateTime
                    End If
                    cmd.Parameters.Add(parameter)
                    parameter = New SqlParameter { _
                        .ParameterName = "@StatusMaxDateTime", _
                        .SqlDbType = SqlDbType.DateTime _
                    }
                    If (opt.StatusMaxDateTime > SqlDateTime.MaxValue.Value) Then
                        parameter.Value = SqlDateTime.MaxValue.Value
                    Else
                        parameter.Value = opt.StatusMaxDateTime
                    End If
                    cmd.Parameters.Add(parameter)
                End If
            End If
            If (Not opt.WorkflowType Is Nothing) Then
                parameter = New SqlParameter("@TypeFullName", opt.WorkflowType.FullName) { _
                    .SqlDbType = SqlDbType.NVarChar, _
                    .Size = &H80 _
                }
                cmd.Parameters.Add(parameter)
                parameter = New SqlParameter("@AssemblyFullName", opt.WorkflowType.Assembly.FullName) { _
                    .SqlDbType = SqlDbType.NVarChar, _
                    .Size = &H80 _
                }
                cmd.Parameters.Add(parameter)
            End If
            If ((Not opt.TrackingDataItems Is Nothing) AndAlso (opt.TrackingDataItems.Count > 0)) Then
                Me.BuildArtifactParameters(cmd, opt.TrackingDataItems)
            End If
            Return cmd
        End Function

        Private Function BuildInstance(ByVal reader As SqlDataReader) As SqlTrackingWorkflowInstance
            Return SqlTrackingQuery.BuildInstance(reader, Me._connectionString)
        End Function

        Friend Shared Function BuildInstance(ByVal reader As SqlDataReader, ByVal connectionString As String) As SqlTrackingWorkflowInstance
            If (reader Is Nothing) Then
                Throw New ArgumentNullException("reader")
            End If
            If reader.IsClosed Then
                Throw New ArgumentException(ExecutionStringManager.InvalidSqlDataReader, "reader")
            End If
            Dim instance As New SqlTrackingWorkflowInstance(connectionString) { _
                .WorkflowInstanceId = reader.GetGuid(1), _
                .WorkflowInstanceInternalId = reader.GetInt64(2), _
                .Initialized = reader.GetDateTime(3) _
            }
            If (DBNull.Value Is reader.Item(4)) Then
                instance.InvokingWorkflowInstanceId = Guid.Empty
            Else
                instance.InvokingWorkflowInstanceId = reader.GetGuid(4)
            End If
            instance.Status = DirectCast(reader.GetInt32(5), WorkflowStatus)
            If Not reader.IsDBNull(6) Then
                instance.WorkflowType = Type.GetType((reader.GetString(6) & ", " & reader.GetString(7)), True, False)
            End If
            Return instance
        End Function

        Private Function GetConnection() As SqlConnection
            If (Me._connectionString Is Nothing) Then
                Throw New InvalidOperationException(ExecutionStringManager.MissingConnectionString)
            End If
            Dim connection As New SqlConnection(Me._connectionString)
            connection.Open
            Return connection
        End Function

        Public Function GetWorkflows(ByVal options As SqlTrackingQueryOptions) As IList([Of] SqlTrackingWorkflowInstance)
            If (options Is Nothing) Then
                Throw New ArgumentNullException("options")
            End If
            If (Not options.TrackingDataItems Is Nothing) Then
                Dim value2 As TrackingDataItemValue
                For Each value2 In options.TrackingDataItems
                    If (value2.QualifiedName Is Nothing) Then
                        Throw New ArgumentNullException("options.TrackingDataItems.QualifiedName")
                    End If
                    If (value2.FieldName Is Nothing) Then
                        Throw New ArgumentNullException("options.TrackingDataItems.FieldName")
                    End If
                Next
            End If
            Dim command As SqlCommand = Me.BuildCommand(options)
            Dim reader As SqlDataReader = Nothing
            Dim list As New List([Of] SqlTrackingWorkflowInstance)
            Try 
                command.Connection = Me.GetConnection
                reader = command.ExecuteReader(CommandBehavior.CloseConnection)
                Do While reader.Read
                    list.Add(Me.BuildInstance(reader))
                Loop
            Finally
                If (Not reader Is Nothing) Then
                    reader.Close
                End If
                If (((Not command Is Nothing) AndAlso (Not command.Connection Is Nothing)) AndAlso (command.Connection.State <> ConnectionState.Closed)) Then
                    command.Connection.Close
                End If
            End Try
            Return list
        End Function

        Public Function TryGetWorkflow(ByVal workflowInstanceId As Guid, <Out> ByRef workflowInstance As SqlTrackingWorkflowInstance) As Boolean
            Dim flag As Boolean
            Dim command As SqlCommand = Me.BuildCommand(workflowInstanceId)
            Dim reader As SqlDataReader = Nothing
            workflowInstance = Nothing
            Try 
                command.Connection = Me.GetConnection
                reader = command.ExecuteReader(CommandBehavior.CloseConnection)
                If reader.Read Then
                    workflowInstance = Me.BuildInstance(reader)
                    Return True
                End If
                flag = False
            Finally
                If (Not reader Is Nothing) Then
                    reader.Close
                End If
                If (((Not command Is Nothing) AndAlso (Not command.Connection Is Nothing)) AndAlso (command.Connection.State <> ConnectionState.Closed)) Then
                    command.Connection.Close
                End If
            End Try
            Return flag
        End Function


        ' Properties
        Public Property ConnectionString As String
            Get
                Return Me._connectionString
            End Get
            Set(ByVal value As String)
                If (value Is Nothing) Then
                    Throw New ArgumentNullException("value")
                End If
                Me._connectionString = value
            End Set
        End Property


        ' Fields
        Private _connectionString As String
    End Class
End Namespace

